![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
IntroductionDatabase management software and the manipulation of data has evolved to where it touches every aspect of our lives. A day doesnt go by in which we dont access a database. Whether we are withdrawing money from an ATM machine, opening a checking account, or purchasing groceries, every aspect of our lives is affected by databases. Hand in hand with the new power of information comes the frustration of having to wait for data to be retrieved. Im sure there isnt a person today who hasnt had to wait for a credit card to be approved. Although the speed of computers has been increasing every year, so has the amount of data being manipulated. Amounts of data that several years ago were unheard of are now a daily part of many companies. In years past, databases were used strictly in the realm of big business because large mainframes cost millions of dollars; today, gigabytes of data are being manipulated on the same types of computer you may have in your own home. No matter how fast new generations of computers get, applications will always be written to take advantage of them. As the cost of storage continues to drop, the amount of data stored will continue to increase. A perfect example of this is the CD-ROM. The advent of the CD-ROM allowed large amounts of data to be inexpensively stored; predictably, many new applications have arisen to take advantage of that technology. These applications are now augmenting written text with video and audio clips. The same type of information revolution is also happening in the database industry. Oracle already has the capability to store video, documents, and large binary objects in the database and allow quick access to this data. Oracle databases can store hundreds of gigabytes of data and can easily retrieve it; Oracle has the potential of storing terabytes of data in a single database in the near future. What is needed to store this kind of data? You must have a Relational Database Management System (RDBMS) with the following attributes:
This book focuses on engineering the performance of Oracle systems while maintaining the attributes just mentioned. To achieve optimal performance, you must incorporate many factors, including engineering performance into the initial design of the database layout, properly constructing the application code, tuning the database, properly selecting the hardware, designing the network, and choosing the operating system. Whats in this Book? In this book, I help you build a foundation of understanding. When you finish reading this book, you should have not only an understanding about how to design high-performance applications but also the knowledge about how the system operates and how each component contributes to the performance of the whole. With this philosophy in mind, the book was designed with six parts, each of which has several chapters. Part I, Introduction, lays down a foundation by presenting an overview of the Oracle architecture and products. You will gain an understanding of the design of Oracle and the different features available. I also discuss what it means to have a well-performing system. If you tune a system without having any goals in mind, you usually end in disappointment. Chapter 4, Tuning Methodology, is an overview of my methodology for system performance tuning. Here you will find the way to plan, execute, and analyze changes that have been made and how to determine what the result means. In Chapter 5, Benchmarking, you learn how to set up a test to show whether your system is performing well and where the problem areas are. You also learn about some of the industry standard benchmarks and what they mean to you. You are introduced to the Transaction Processing Performance Council (TCP), their Web site, and how you can use this information. Chapter 6, Performance Monitoring Tools, gives an overview of some of the performance monitoring tools available from Oracle and third-party vendors and describes how they can help you in investigating performance problems. Chapter 7, Performance Engineering Starts at the Design Stage, explains the holistic approach to performance engineering. Performance should be engineered into every aspect of the system, from hardware selection and network configuration to database design and application development. Part II, Tuning the Server, focuses on tuning the RDBMS server. The performance of the system depends on a number of factors. By analyzing each component individually, you can determine where performance can be enhanced. Chapter 8, What Affects Oracle Server Performance? is an overview of what can effect the server performance and where bottlenecks can occur. Chapter 9, Oracle Instance Tuning, reviews the bulk of instance tuning. Items such as memory, I/O, and CPU usage are discussed here. You also get a more in-depth look at some internal Oracle structures. Chapter 10, Performance Enhancements, looks at some of the things you can do in the RDBMS server to improve performance. Some examples are the use of clusters and indexes as well as some of Oracles parallel features. Chapter 11, Tuning the Server Operating System, discusses the OS parameters you can tune. Chapter 12, Operating System-Specific Tuning, focuses on a few of the 90 platforms Oracle supports. Chapter 13, System Processors, Chapter 14, Advanced Disk I/O Concepts, and Chapter 15, Disk Arrays, describe some specifics concerning hardware and include detailed explanations about why different hardware features affect RDBMS performance. Part III, Configuring the System, looks at differences in design, tuning, and configuration based on the type of application to be run. This part of the book is designed to give you an understanding of the differences in data access patterns and system loading based on your application profile. In addition to explaining the differences in system workload, the chapters in this part of the book provide solutions to the problems these workloads cause. Part IV, Tuning SQL, and Part V, Tuning the Client, explore the client. Here, the focus is on the application and the front-end pieces and how to improve the clients performance. Part IV focuses on the SQL statements used in your application; Part V looks more at the client machines themselves and how you can tune them. Chapter 24, What Is a Well-Tuned SQL Statement? introduces the concept of badly tuned SQL statements and how they can affect the performance of the client and the server. It is here that the importance of a well-tuned SQL statement is discussed. The remainder of Part IV looks at various topics relating to how your SQL statements can be improved and tuned. Included are many tips and hints on features and techniques that can improve your SQL statements. Part V focuses on the client OS and improvements you can make to streamline the system. The goal of tuning the client OS is to get it out of the way of the client application so that the client application can be more efficient. Chapter 34, Using GUI Builders, examines the popular GUI builder products available on the market and how you can tune the resulting application to improve performance. Part VI, Tuning the Network, is what is left over when you have tuned the server and the application. It covers how you know if you have a network problem and how you solve it. How To Use this Book To keep the book interesting, I have woven in some personal anecdotes relevant to the subject matter. I hope I have conveyed some of the excitement that comes when you push systems to their limits. Those of us who work in the database performance field constantly push the envelope of technology to achieve new levels of performance previously thought impossible. This kind of experimentation can be very satisfying when everything works well; it can be frustrating when it doesnt. My hopes are that, having read this book, you have a basic understanding of how the components of the system work together to form the whole. If you have this foundation, I feel confident that you can tackle a performance problem, know what to look for, and know how to fix it. Not all performance problems are alike, nor are all solutions. It is important that you have a basic understanding of what to look for and what the possible solutions are. To me, performance is one of the most exciting areas of computing. Personal computers now have the computing power reserved for mainframes just a few years ago. Gigabyte hard disks for less than $300 now make it possible for us to have huge stores of information in our home computers. But performance does not mean buying the biggest and the fastest; you have to get the most out of what youve got. Buying a bigger or a faster server may be a waste if, in fact, the problem is the client machine or the network: a 10-second response time to a query could be caused by a 1-second response from the server and a 9-second wait for the information to be processed in the GUI. If I have done my job correctly, you should finish this book with the ability to analyze the problem, hypothesize a solution, test that solution, and understand the result. I hope this book gives novices an idea of what performance engineering is all about; seasoned professionals should receive some new insight and ideas.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |